/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Model.ImageBean;
import Model.PropertyBean;
import java.awt.Image;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author amkreuz
 */
public class PropertyDAO {

    private static Connection DBConn;

    public static void init() throws SQLException {
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
        } catch (ClassNotFoundException e) {
            System.err.println(e.getMessage());
            System.exit(0);
        }
        DBConn = DriverManager.getConnection("jdbc:derby://localhost:1527/FarmDB");
    }

    public static void terminate() throws SQLException {
        DBConn.close();
    }

    public static void createProperty(PropertyBean bean) throws SQLException {
        Statement stmt = DBConn.createStatement();
        //handle purchased and image path and email
        String insert = "insert into property values ('" + bean.getAddress() + "','" + bean.getCity() + "','" + bean.getState() + "'," + bean.getZip() + "," + bean.getPrice() + "," + bean.getAcre() + ",'" + bean.getDescription() + "'," + bean.getBedrooms() + "," + bean.getBathrooms() + "," + bean.getHalfbaths() + "," + bean.isIsHighlight() + ",'" + bean.getEmail() + "'," + bean.isIsPurchased() + ")";
        stmt.executeUpdate(insert);
    }

    public static ArrayList<PropertyBean> returnProperties(int price, int acre) throws SQLException {
        ArrayList<PropertyBean> allProperties = new ArrayList();
        Statement stmt = DBConn.createStatement();

        String selectStatement = "SELECT * FROM property where price>=" + price + " and acre>=" + acre;
        ResultSet rs = stmt.executeQuery(selectStatement);

        // Now to start grabbing and setting variables in the property.
        while (rs.next()) {
            PropertyBean newBean = new PropertyBean();
            newBean.setAddress(rs.getString("address"));
            newBean.setCity(rs.getString("city"));
            newBean.setState(rs.getString("statecode"));
            newBean.setZip(rs.getInt("zip"));
            newBean.setPrice(rs.getInt("price"));
            newBean.setAcre(rs.getInt("acre"));
            newBean.setDescription(rs.getString("description"));
            newBean.setBedrooms(rs.getInt("bedrooms"));
            newBean.setBathrooms(rs.getInt("bathrooms"));
            newBean.setHalfbaths(rs.getInt("halfbaths"));
            newBean.setIsHighlight(rs.getBoolean("highlight"));
            newBean.setEmail(rs.getString("email"));
            newBean.setIsPurchased(rs.getBoolean("purchased"));
            if (newBean.isIsHighlight()) {
                allProperties.add(0, newBean);
            } else {
                allProperties.add(newBean);
            }
        }
        return allProperties;
    }

    public static int getImages(PropertyBean bean) throws SQLException {
        Statement stmt = DBConn.createStatement();

        ResultSet rs = stmt.executeQuery("Select * from image where address='" + bean.getAddress() + "' and zip=" + bean.getZip());

        int result = 0;
        while (rs.next()) {
            result++;
        }

        return result;
    }

    public static void updateImagePath(PropertyBean bean, String path) throws SQLException {
        Statement stmt = DBConn.createStatement();
        stmt.executeUpdate("insert into image values ('" + path + "','" + bean.getAddress() + "'," + bean.getZip() + ")");
    }

    public static void purchaseLand(ArrayList<String> names) throws SQLException {
        Statement stmt = DBConn.createStatement();
        String update;
        for (int i = 0; i < names.size(); i++) {
            update = "UPDATE Property"
                    + " SET Purchased='True'"
                    + " WHERE Address='" + names.get(i) + "";
            stmt.executeUpdate(update);
        }
    }

    public static ArrayList<PropertyBean> getHighlights() throws SQLException {
        ArrayList<PropertyBean> list = new ArrayList<>();

        Statement stmt = DBConn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from Property where highlight=true");


        while (rs.next()) {
            PropertyBean newBean = new PropertyBean();
            newBean.setAddress(rs.getString("address"));
            newBean.setCity(rs.getString("city"));
            newBean.setState(rs.getString("statecode"));
            newBean.setZip(rs.getInt("zip"));
            newBean.setPrice(rs.getInt("price"));
            newBean.setAcre(rs.getInt("acre"));
            newBean.setDescription(rs.getString("description"));
            newBean.setBedrooms(rs.getInt("bedrooms"));
            newBean.setBathrooms(rs.getInt("bathrooms"));
            newBean.setHalfbaths(rs.getInt("halfbaths"));
            newBean.setIsHighlight(rs.getBoolean("highlight"));
            newBean.setEmail(rs.getString("email"));
            newBean.setIsPurchased(rs.getBoolean("purchased"));
            Statement stmt2 = DBConn.createStatement();
            ResultSet rs2 = stmt2.executeQuery("select * from image where address='"+newBean.getAddress()+"' and zip="+newBean.getZip());
            ArrayList<ImageBean> images = new ArrayList<>();
            while (rs2.next()) {
                ImageBean i = new ImageBean();
                i.setPath(rs2.getString("filepath"));
                images.add(i);
            }
            newBean.setImageList(images);
            list.add(newBean);
        }
        
        return list;
    }
}
